using System;
using System.Data;
using System.Drawing;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;

namespace CoreApp.Utility
{
    /// <summary>
    /// EPPlus.Core导入导出Excel帮助类。
    /// 支持读写Excel 2007/2010 文件，不支持导出2003版的Excel(xls后缀)
    /// </summary>
    public class EPPlusHelper
    {
        /// <summary>
        /// 导出excel
        /// 调用方式：
        /// 1、在调用的地方，直接在html页面执行window.location.href="文件相对路径"，即可下载。
        ///     例如：window.location.href='/excel/20190325195219495.xlsx';
        /// 2、在调用的地方，return File(new FileStream(filePath, FileMode.Open), "application/octet-stream", fileName);
        /// </summary>
        /// <param name="ds">DataSet(一个DataTable对应excel里的一个sheet)</param>
        /// <param name="filePath">excel路径</param>
        /// <param name="isHasheader">是否存在表头</param>
        /// <returns></returns>
        public static bool ExportExcel(DataSet ds,string filePath,bool isHasheader)
        {
            try{
                if (ds == null || ds.Tables.Count == 0 || string.IsNullOrWhiteSpace(filePath))
                    return false;

                FileInfo file = new FileInfo(filePath);

                using (ExcelPackage package = new ExcelPackage(file))
                {
                    foreach (DataTable dt in ds.Tables)
                    {
                        //创建工作表worksheet
                        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add(dt.TableName);

                        int rowCount = dt.Rows.Count;
                        int columnCount = dt.Columns.Count;
                        for (int i = 0; i < rowCount; i++)
                        {
                            for (int j = 0; j < columnCount; j++)
                            {
                                worksheet.Cells[i + 1, j + 1].Value = dt.Rows[i][j].ToString();
                                worksheet.Column(j + 1).Width = 30;//设置列宽
                            }
                        }

                        //设置表头样式
                        if(isHasheader)
                        {
                            using (var cell = worksheet.Cells[1, 1, 1, columnCount])
                            {       
                                cell.Style.Font.Size = 14;                         
                                cell.Style.Font.Bold = true; //加粗
                                cell.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center; //水平居中
                                cell.Style.VerticalAlignment = ExcelVerticalAlignment.Center; //垂直居中                                                               
                                cell.Style.Fill.PatternType = ExcelFillStyle.Solid; //背景颜色
                                cell.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(60,179,113)); //设置单元格背景色
                            }
                        }
                    }

                    //保存
                    package.Save();
                }

                return true;
            }
            catch (Exception)
            {
                 return false;
            }           
        }

        /// <summary>
        /// 导入excel
        /// </summary>
        /// <param name="file">excel文件</param>
        /// <param name="isHasheader">是否存在表头</param>
        /// <returns></returns>
        public static DataSet ImportExcel(FileInfo file,bool isHasheader)
        {
            if(file==null)
                return null;

            DataSet ds = null;
            using (ExcelPackage package = new ExcelPackage(file))
            {                
                int WorksheetsCount = package.Workbook.Worksheets.Count;
                if(WorksheetsCount>0)
                {
                    //创建DataSet
                    ds = new DataSet();
                    foreach (ExcelWorksheet worksheet in package.Workbook.Worksheets)
                    {
                        DataTable dt = new DataTable();
                        dt.TableName=worksheet.Name;//表名

                        int rowCount = worksheet.Dimension.Rows;
                        int colCount = worksheet.Dimension.Columns;
                        for (int row = 0; row < rowCount; row++)
                        {
                            //表头
                            if(isHasheader && row==0)
                            {
                                for (int col = 0; col < colCount; col++)
                                {
                                    dt.Columns.Add(worksheet.Cells[row+1, col+1].Value.ToString());
                                }                            
                            }
                            else
                            {
                                DataRow dr = dt.NewRow();
                                for (int col = 0; col < colCount; col++)
                                {
                                    dr[col] = worksheet.Cells[row+1, col+1].Value.ToString();
                                }
                                dt.Rows.Add(dr);
                            }
                        }
                        ds.Tables.Add(dt);
                    }
                }
            }

            return ds;
        }
    }
}